/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.dusin.servlet.common;

import com.tlf.dyjc5.dao.CommonComboDao;
import com.tlf.dyjc5.service.PointService;
import com.tlf.dyjc5.service.YsjService;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItemIterator;
import org.apache.commons.fileupload.FileItemStream;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.dusin.commonutil.CommonUtil;
import org.dusin.commonutil.DateUtil;
import org.dusin.web.util.JsonUtil;
import org.dusin.web.util.TheUtil;
import org.springframework.context.ApplicationContext;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.web.context.support.WebApplicationContextUtils;

/**
 *
 * @author tengyu
 */
public class UploadImportExcelPointListServlet extends HttpServlet {

    static Logger logger = Logger.getLogger(UploadImportExcelPointListServlet.class);

    /**
     * Processes requests for both HTTP
     * <code>GET</code> and
     * <code>POST</code> methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        String savePath = this.getInitParameter("SavePath");

        try {
            /*SmartUpload smartUpload = new SmartUpload();
             javax.servlet.jsp.PageContext pageContext = javax.servlet.jsp.JspFactory.getDefaultFactory().getPageContext(this, request, response, null, true, 8192, true);
             smartUpload.initialize(pageContext);
             smartUpload.upload();
             System.out.println(savePath);
             smartUpload.save(savePath);
             //smartUpload.getFiles().getFile(0).*/

            List removeDataList = new ArrayList();
            List createDataList = new ArrayList();

            ApplicationContext context = WebApplicationContextUtils.getWebApplicationContext(getServletContext());
            PointService pointService = (PointService) context.getBean("pointServiceProxy");
            CommonComboDao commonDao = (CommonComboDao) context.getBean("commonDao");

            String currentOrgCode = (String) request.getSession().getAttribute("currentOrgCode");


            Map pointTypeMap = createMap(commonDao, "id", "name", "t_pointType", "where orgCode='01' and orgCode='" + currentOrgCode + "'");
            Map pointType2Map = createMap(commonDao, "id", "name", "t_pointType2", "where orgCode='01' and orgCode='" + currentOrgCode + "'");
            Map orgMap = createMap(commonDao, "orgCode", "name", "t_org", "where orgCode like '" + currentOrgCode + "%'");
            Map voltageLevelMap = createMap(commonDao, "id", "name", "t_voltageLevel", "");
            Map deviceTypeMap = createMap(commonDao, "id", "deviceType", "t_deviceType", "");
            Map modelMap = createMap(commonDao, "id", "model", "t_model", "");
            Map manufactoryMap = createMap(commonDao, "id", "name", "t_manufactory", "");
            Map commTypeMap = createMap(commonDao, "id", "name", "t_commType", "");

            //boolean isMultipart = ServletFileUpload.isMultipartContent(request);
            //System.out.println("isMultipart:" + isMultipart);

            // Create a factory for disk-based file items
            DiskFileItemFactory factory = new DiskFileItemFactory();

// Configure a repository (to ensure a secure temp location is used)
            ServletContext servletContext = this.getServletConfig().getServletContext();
            File repository = (File) servletContext.getAttribute("javax.servlet.context.tempdir");
            //System.out.println("repository:" + repository.getAbsolutePath());
            factory.setRepository(repository);

// Create a new file upload handler
            ServletFileUpload upload = new ServletFileUpload(factory);

// Parse the request
            //List<FileItem> items = upload.parseRequest(request);

            // Parse the request
            FileItemIterator iter = upload.getItemIterator(request);

            //List<Map<String, Object>> errorList = new ArrayList<Map<String, Object>>();  //存放{行号,错误信息}
            StringBuilder sb = new StringBuilder();
            int count = 0;   //统计总共插入了多少条数据
            int totalCount = 0;
            while (iter.hasNext()) {
                FileItemStream item = iter.next();
                String name = item.getFieldName();
                InputStream stream = item.openStream();
                if (item.isFormField()) {
                    //System.out.println("Form field " + name + " with value "
                    //        + Streams.asString(stream) + " detected.");
                } else {
                    logger.info("File field " + name + " with file name "
                            + item.getName() + " detected.");
                    // Process the input stream


                    Workbook wb = new HSSFWorkbook(stream);
                    boolean hasNext = true;

                    Sheet sheet1 = wb.getSheetAt(0);


                    int i = 7;//第八行开始填写
                    while (hasNext) {

                        Row r = sheet1.getRow(i++);
                        if (r == null) {
                            hasNext = false;
                            break;
                        }
                        totalCount++;
                        Cell cell;

                        try {
                            String subCompany = getStringValue(r.getCell(2, Row.RETURN_BLANK_AS_NULL));
                            String station = getStringValue(r.getCell(3, Row.RETURN_BLANK_AS_NULL));
                            String orgCode = (String) orgMap.get(station);//获取供电所的orgCode
                            if (orgCode == null || "".equals(orgCode.trim())) {
                                orgCode = (String) orgMap.get(subCompany);//如果没要供电所的orgCode则获取部门分县局的orgCode
                            }
                            if (orgCode == null || "".equals(orgCode.trim())) {  //如果orgCode还是空，则跳过该条记录
                                //Map errorItem = new HashMap<String, Object>();
                                //errorItem.put(new Integer(i).toString(), "缺少有效的部门分县局或供电所");
                                //errorList.add(errorItem);
                                sb.append(i).append("-").append("缺少有效的部门分县局或供电所").append("<br/>");
                                continue;
                            }

                            String code = getStringValue(r.getCell(4, Row.RETURN_BLANK_AS_NULL));   //监测点编码
                            
                            cell = r.getCell(5);  //监测点名称
                            if (cell == null || cell.getRichStringCellValue() == null
                                    || "".equals(cell.getRichStringCellValue().getString().trim())) {    //若无监测点名称，跳过
                                //Map errorItem = new HashMap<String,Object>();
                                //errorItem.put(new Integer(i).toString(), "缺少监测点名称");
                                //errorList.add(errorItem);
                                sb.append(i).append("-").append("缺少监测点名称").append("<br/>");
                                continue;
                            }
                            String pointName = cell.getRichStringCellValue().getString();


                            
                            String deviceNo = getStringValue(r.getCell(28, Row.RETURN_BLANK_AS_NULL));
                            if (deviceNo == null || "".equals(deviceNo.trim())) {    //若无监测点名称，表示行结束，所以导入数据Excel表格中不能有空行，否则空行后的数据不导入
                                //Map errorItem = new HashMap<String, Object>();
                                //errorItem.put(new Integer(i).toString(), "缺少终端编码");
                                //errorList.add(errorItem);
                                sb.append(i).append("-").append("缺少装置编码").append("<br/>");
                                continue;
                            }
                            
                            String pointType = getStringValue(r.getCell(6, Row.RETURN_BLANK_AS_NULL));
                            Long pointTypeId = (Long) pointTypeMap.get(pointType);
                            if (pointTypeId == null) {
                                pointTypeId = 0L;
                            }

                            String pointType2 = getStringValue(r.getCell(7, Row.RETURN_BLANK_AS_NULL));
                            Long pointType2Id = (Long) pointType2Map.get(pointType2);
                            if (pointType2Id == null) {
                                pointType2Id = 0L;
                            }                           

                            String voltageLevel = getStringValue(r.getCell(8, Row.RETURN_BLANK_AS_NULL));
                            Long voltageLevelId = (Long) voltageLevelMap.get(voltageLevel.toUpperCase());
                            if (voltageLevelId == null) {
                                voltageLevelId = 0L;
                            }

                            Double voltageUpperLimit = 0.0;
                            try {
                                voltageUpperLimit = Double.parseDouble(getStringValue(r.getCell(9, Row.RETURN_BLANK_AS_NULL)));
                            } catch (Exception e) {
                            }

                            Double voltageLowerLimit = 0.0;
                            try {
                                voltageLowerLimit = Double.parseDouble(getStringValue(r.getCell(10, Row.RETURN_BLANK_AS_NULL)));
                            } catch (Exception e) {
                            }

                            String pointArea = getStringValue(r.getCell(11, Row.RETURN_BLANK_AS_NULL));
                            String pointPosition = getStringValue(r.getCell(12, Row.RETURN_BLANK_AS_NULL));

                            String publicName = getStringValue(r.getCell(13, Row.RETURN_BLANK_AS_NULL));  //公变名称

                            String stationVoltageLevel = getStringValue(r.getCell(14, Row.RETURN_BLANK_AS_NULL));
                            Long stationVoltageLevelId = (Long) voltageLevelMap.get(stationVoltageLevel.toUpperCase());
                            if (stationVoltageLevelId == null) {
                                stationVoltageLevelId = 0L;
                            }

                            String stationName = getStringValue(r.getCell(15, Row.RETURN_BLANK_AS_NULL));  //供电用户名称
                            String userName = getStringValue(r.getCell(16, Row.RETURN_BLANK_AS_NULL));  //供电用户名称
                            
                            String reportStatus = getStringValue(r.getCell(17, Row.RETURN_BLANK_AS_NULL));
                            String startStatus = getStringValue(r.getCell(18, Row.RETURN_BLANK_AS_NULL));
                            int status = TheUtil.getPointStatusCode(startStatus, reportStatus);
                            
                            String effectTime = getStringValue(r.getCell(19, Row.RETURN_BLANK_AS_NULL));
                            String stopTime = getStringValue(r.getCell(20, Row.RETURN_BLANK_AS_NULL));
                            String stopReason = getStringValue(r.getCell(21, Row.RETURN_BLANK_AS_NULL));
                            
                            String installTime = getStringValue(r.getCell(22, Row.RETURN_BLANK_AS_NULL));
                            String installSite = getStringValue(r.getCell(23, Row.RETURN_BLANK_AS_NULL));
                            String manufactoryName = getStringValue(r.getCell(24, Row.RETURN_BLANK_AS_NULL));
                            Long manufactoryId = (Long) manufactoryMap.get(manufactoryName);
                            if (manufactoryId == null) {
                                manufactoryId = 0L;
                            }


                            String deviceType = getStringValue(r.getCell(25, Row.RETURN_BLANK_AS_NULL));
                            Long deviceTypeId = (Long) deviceTypeMap.get(deviceType);
                            if (deviceTypeId == null) {
                                deviceTypeId = 0L;
                            }

                            String model = getStringValue(r.getCell(26, Row.RETURN_BLANK_AS_NULL));
                            Long modelId = (Long) modelMap.get(model.toUpperCase());
                            if (modelId == null) {
                                modelId = 0L;
                            }

                            String commType = getStringValue(r.getCell(27, Row.RETURN_BLANK_AS_NULL));
                            Long commTypeId = (Long) commTypeMap.get(commType.toUpperCase());
                            if (commTypeId == null) {
                                commTypeId = 0L;
                            }

                            String mobileNo = getStringValue(r.getCell(28, Row.RETURN_BLANK_AS_NULL));
                            String terminalNo = getStringValue(r.getCell(30, Row.RETURN_BLANK_AS_NULL));
                            String uniqueCode = getStringValue(r.getCell(31, Row.RETURN_BLANK_AS_NULL));
                            String zbm = getStringValue(r.getCell(32, Row.RETURN_BLANK_AS_NULL));
                            String areaCode = getStringValue(r.getCell(33, Row.RETURN_BLANK_AS_NULL));
                            String channel = getStringValue(r.getCell(34, Row.RETURN_BLANK_AS_NULL));
                            
                            String produceTime = getStringValue(r.getCell(35, Row.RETURN_BLANK_AS_NULL));
                            String checkTime = getStringValue(r.getCell(36, Row.RETURN_BLANK_AS_NULL));
                            String expireTime = getStringValue(r.getCell(37, Row.RETURN_BLANK_AS_NULL));  //有效期
                            if (channel != null) {
                                channel = channel.substring(0, 1);
                            }
                            
                            String ip = getStringValue(r.getCell(38, Row.RETURN_BLANK_AS_NULL));                           
                            String port = getStringValue(r.getCell(39, Row.RETURN_BLANK_AS_NULL));
                            
                            
                            String note = getStringValue(r.getCell(41, Row.RETURN_BLANK_AS_NULL));

                            pointService.trAddNewPointDevice(new Object[]{0, pointName, pointTypeId, pointType2Id, DateUtil.get(), DateUtil.get(), status, voltageLevelId, voltageLowerLimit,
                                voltageUpperLimit, effectTime, note, orgCode, code, userName, publicName, pointArea, pointPosition, stationVoltageLevelId, stationName, installTime, stopTime, stopReason,
                                deviceNo, modelId, installSite, mobileNo, terminalNo, channel, uniqueCode, areaCode, ip, port, 0L, DateUtil.get(), produceTime, checkTime, expireTime, status % 10, "", DateUtil.get(), DateUtil.get(), orgCode, zbm
                            });
                            count++;
                        } catch (DuplicateKeyException e) {
                            String msg = e.getMessage();
                            if (msg.indexOf("deviceNo") != -1) {
                                sb.append(i).append("-").append("设备编号deviceNo不能和现有数据库中的记录重复").append("<br/>");
                            }else if(msg.indexOf("point_name")!=-1){
                                sb.append(i).append("-").append("监测点名称不能和现有数据库中的记录重复").append("<br/>");
                            }
                            e.printStackTrace();
                        } catch (Exception e) {  //跳过不完整数据行
                            //Map errorItem = new HashMap<String, Object>();
                            //errorItem.put(new Integer(i).toString(), e.getMessage());
                            //errorList.add(errorItem);
                            sb.append(i).append("-").append(e.getMessage()).append("<br/>");
                            e.printStackTrace();
                        }
                    }

                }
            }
            //ysjService.trAddAll(removeDataList, createDataList);
            //Thread.sleep(30000);
            sb.insert(0, "处理记录总行数:" + totalCount + ",成功入库:" + count + "<br/>");
            logger.warn(sb.toString());
            out.println(JsonUtil.createSuccessJsonResponse(sb.toString()));
        } catch (Exception e) {
            e.printStackTrace();
            out.println(JsonUtil.createFailureJsonResponse(e.getMessage()));
        } finally {
            out.close();
        }
    }

    private String getStringValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                return (cell.getRichStringCellValue().getString().trim());

            case Cell.CELL_TYPE_NUMERIC:
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    return DateUtil.get(cell.getDateCellValue());
                } else {
                    return CommonUtil.formatNumber(cell.getNumericCellValue(), "#.#");
                }

            case Cell.CELL_TYPE_BOOLEAN:
                return Boolean.toString(cell.getBooleanCellValue());

            case Cell.CELL_TYPE_FORMULA:
                return (cell.getCellFormula());

            default:
                return null;
        }

    }

    private Map createMap(CommonComboDao commonDao, String id, String name, String tableName, String condition) {
        List<Map<String, Object>> recordList = commonDao.findInfoWithCondition(id, name, tableName, condition);
        Map<String, Object> resultMap = new HashMap<String, Object>();
        for (Map map : recordList) {     //将List转换成键值对（name->id）
            resultMap.put((String) map.get(name), map.get(id));
        }
        return resultMap;
    }
    
    

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP
     * <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP
     * <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}
